Skip to main content

04 表空间 用户 权限

Oracle表空间​

表空间是 Oracle 数据库中用于逻辑存储管理的单元，是一组数据文件的集合，用于存储数据库对象（如表、索引、视图等）。它将物理的磁盘文件与逻辑的数据库对象进行了分层管理，方便数据库的存储规划、性能优化和维护操作

Oracle表空间分类​

  1. 永久表空间：用于存储永久性数据的表空间，这些数据在数据库生命周期内长期存在，如用户创建的表、索引、存储过程等。特点是数据一旦写入，除非显式删除，否则会一直保留；是数据库中存储业务数据的主要载体。
  2. 临时表空间：用于存储临时数据的表空间，这些数据仅在操作执行期间存在，操作结束后会被自动清理，如排序操作的中间结果、临时表等。特点是数据具有临时性，不持久保存；独立于永久表空间，避免临时操作对业务数据存储的性能干扰。TEMP是 Oracle 默认的临时表空间，所有用户若未指定临时表空间，都会使用它。

创建表空间的命令

-- 创建永久表空间
CREATE TABLESPACE 表空间名 
DATAFILE '数据文件路径/数据文件名.dbf' 
SIZE 初始大小（如100M） 
AUTOEXTEND ON NEXT 自动扩展大小（如10M） 
MAXSIZE 最大大小（如UNLIMITED或500M） 
EXTENT MANAGEMENT LOCAL AUTOALLOCATE -- 本地自动管理区（推荐）
SEGMENT SPACE MANAGEMENT AUTO; -- 自动段空间管理（推荐）

-- 创建临时表空间

CREATE TEMPORARY TABLESPACE 临时表空间名 
TEMPFILE '临时文件路径/临时文件名.dbf' 
SIZE 初始大小（如50M） 
AUTOEXTEND ON NEXT 扩展大小（如10M） MAXSIZE 最大大小（如200M） 
EXTENT MANAGEMENT LOCAL UNIFORM SIZE 区大小（如1M）; -- 临时表空间推荐统一区大小

示例

--创建一个名为ORCLSTART的表空间
CREATE TABLESPACE ORCLSTART
DATAFILE 'T:\Oracle\ORCL11G\oradata\orcl\ORCLSTART.DBF'
SIZE 100M
AUTOEXTEND ON NEXT 100M
MAXSIZE UNLIMITED 
EXTENT MANAGEMENT LOCAL AUTOALLOCATE
SEGMENT SPACE MANAGEMENT AUTO;

创建表空间必须使用sys用户或者system用户。

创建用户​

使用PLSQL登陆system用户创建用户对象。

PLSQL可以查看创建用户的sql语句

-- Create the user 
create user ORCLSTART
  identified by test6344
  default tablespace ORCLSTART
  temporary tablespace TEMP
  profile DEFAULT;

登陆此用户发现 报错信息：此用户缺少创建session权限，登陆失败。

分配用户权限​

分配用户权限也只能在system sys用户操作。

  1. 对象权限：对象权限是指在指定的表、视图、序列上指定执行动作的权限或权利。
  2. 角色权限：是可以授予用户相关权限的组，包含对象权限和系统权限，让权限的分配授予和撤回权限更方便。
  3. 系统权限：为用户分配创建表、创建用户，创建存储过程等 权限。是作用于数据库实例上的。

-- Grant/Revoke role privileges 
grant connect to ORCLSTART;
grant dba to ORCLSTART;
-- Grant/Revoke system privileges 
grant create any table to ORCLSTART;
grant create any index to ORCLSTART;
grant create any sequence to ORCLSTART;
grant create any view to ORCLSTART;
grant unlimited tablespace to ORCLSTART;

创建表结构​

登陆ORCLSTART用户，执行创建表的操作。

创建用户表

用户表字段

用户表主外键、索引

生成创建表SQL

-- Create table
-- Create table
create table USERS
(
  id   NUMBER not null,
  name VARCHAR2(50) not null
)
tablespace ORCLSTART
  pctfree 10
  initrans 1
  maxtrans 255
  storage
  (
    initial 64K
    next 1M
    minextents 1
    maxextents unlimited
  );
-- Add comments to the table 
comment on table USERS
  is '创建用户表';
-- Add comments to the columns 
comment on column USERS.id
  is '用户表主键ID';
comment on column USERS.name
  is '用户名';
-- Create/Recreate primary, unique and foreign key constraints 
alter table USERS
  add constraint USERS_PK primary key (ID)
  using index
  tablespace ORCLSTART
  pctfree 10
  initrans 2
  maxtrans 255
  storage
  (
    initial 64K
    next 1M
    minextents 1
    maxextents unlimited
  );
-- Grant/Revoke object privileges 
grant select, insert, update on USERS to SCOTT;


表对象的权限分配​

表对象的权限分配：

这里ORCLSTART用户的USERS表被授予给了SCOTT用户，具有查询插入更新数据的权限，不能进行删除数据 引用外键 修改表结构 。

权限项含义​

权限项作用说明
选择（SELECT）允许查询表中数据（如SELECT * FROM 表名;）
插入（INSERT）允许向表中插入新数据（如INSERT INTO 表名 ...）
更新（UPDATE）允许修改表中已有数据（如UPDATE 表名 SET ...）
删除（DELETE）允许删除表中数据（如DELETE FROM 表名 ...）
引用（REFERENCES）允许在其他表中以该表的列为外键（FOREIGN KEY）
修改（ALTER）允许修改表结构（如添加列、修改数据类型等，ALTER TABLE 表名 ...）
Index允许在表上创建索引（CREATE INDEX ... ON 表名 ...）
Debug若表关联 PL/SQL 对象（如触发器、存储过程），允许调试；表本身无实际作用

权限分配策略（按用户角色划分）​

1. 管理员 / 开发人员（如SYSTEM、DBA角色用户）​

  • 推荐权限：全选（SELECT、INSERT、UPDATE、DELETE、REFERENCES、ALTER、Index）。
  • 原因：管理员需维护表结构、测试数据操作；开发人员需调试业务逻辑、创建索引优化性能。
  • 示例：如截图中SYSTEM用户的全权限分配，适合数据库管理和开发场景。

2. 普通业务用户（如SCOTT、自定义业务用户）​

  • 基础数据操作权限：SELECT（查询）、INSERT（新增）、UPDATE（修改）、DELETE（删除）。
    • 若用户仅需 “查询 + 新增”，可仅授予SELECT、INSERT；
    • 若涉及数据维护，再补充UPDATE、DELETE。
  • 高级权限（按需分配）：
    • REFERENCES：仅当用户需在其他表中引用该表作为外键时授予；
    • ALTER、Index：通常不授予普通用户，避免误改表结构或创建无效索引。

3. 只读用户（如报表用户、审计用户）​

  • 仅授予SELECT权限，确保用户只能查询数据，无法修改、删除或变更表结构。

4. 集成用户（如中间件、ETL 工具用户）​

  • 根据集成场景分配：
    • 若工具仅需 “读取数据”：授予SELECT；
    • 若需 “同步 / 导入数据”：补充INSERT；
    • 若需 “更新业务状态”：补充UPDATE；
    • 一般不授予ALTER、Index、DELETE（避免工具误操作）。

权限分配的最佳实践​

  1. 遵循 “最小权限原则”：只授予用户完成工作必需的权限，避免过度授权导致数据风险。
  2. 通过角色（Role）批量管理：将常用权限组合成角色（如DATA_OPERATOR角色包含SELECT、INSERT、UPDATE、DELETE），再将角色授予用户，简化权限维护。
  3. 定期审计权限：通过DBA_TAB_PRIVS视图查询表的权限分配情况，清理冗余或不合理的授权。

在Oracle数据库的权限管理中，Grantable（可授予） 是指被授予权限的用户是否有权将该权限再授予其他用户。

  • 若Grantable标记为**YES**，表示该用户不仅自己拥有此权限，还可以将这个权限授予其他用户或角色；
  • 若Grantable标记为**NO**，表示该用户仅自己拥有此权限，无法将其转授给他人。

例如，执行命令 GRANT SELECT ON USERS TO ANONYMOUS WITH GRANT OPTION; 后，ANONYMOUS用户对USERS表的SELECT权限的Grantable就会是YES，即ANONYMOUS可以将USERS表的SELECT权限再授予其他用户。